package org.jeecg.modules.online.cgreport.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.aspect.annotation.PermissionData;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.common.system.vo.DynamicDataSourceModel;
import org.jeecg.common.util.BrowserUtils;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.online.cgreport.def.CgReportConstant;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportHead;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportItem;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportParam;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportItemService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportParamService;
import org.jeecg.modules.online.cgreport.service.impl.OnlCgreportHeadServiceImpl;
import org.jeecg.modules.online.cgreport.util.CgReportQueryParamUtil;
import org.jeecg.modules.online.cgreport.util.SqlUtil;
import org.jeecg.modules.online.config.util.DbTableUtil;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.params.ExcelExportEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;

/**
 * @Title: Controller
 * @Description: 在线报表配置
 * @Author: jeecg-boot
 * @Date: 2019-03-08
 * @Version: V1.0
 */
@Slf4j
@RestController("onlCgreportAPI")
@RequestMapping("/online/cgreport/api")
public class OnlCgreportAPI {

	@Autowired
	private OnlCgreportHeadServiceImpl onlCgreportHeadService;
	@Autowired
	private IOnlCgreportItemService onlCgreportItemService;
	@Autowired
	private ISysBaseAPI sysBaseAPI;
	@Autowired
	private IOnlCgreportParamService onlCgreportParamService;

	/**
	 * 通过 head code 获取 所有的 数据和字段item，并生成 columns 所需的 json
	 *
	 * @param code
	 *            head code
	 * @return
	 * @throws Exception
	 */
	@GetMapping(value = "/getColumnsAndData/{code}")
	@PermissionData
	public Result<?> getColumnsAndData(@PathVariable("code") String code, HttpServletRequest request) {
		OnlCgreportHead head = onlCgreportHeadService.getById(code);
		if (head == null) {
			return Result.error("实体不存在");
		}
		// 根据code获取所有数据
		Result result = this.getData(code, request);
		if (result.getCode().equals(200)) {
			// 查询出该表的数据
			JSONObject resultJson = JSON.parseObject(JSONObject.toJSONString(result.getResult()));
			JSONArray records = resultJson.getJSONArray("records");

			// 查询出该表的 columns
			QueryWrapper<OnlCgreportItem> queryWrapper = new QueryWrapper<>();
			queryWrapper.eq("cgrhead_id", code).eq("is_show", 1).orderByAsc("order_num");

			// 获取CgreportItem列
			List<OnlCgreportItem> list = onlCgreportItemService.list(queryWrapper);


			Map<String, Object> dictOptions = new HashMap<>();
			// 用于在页面上遍历 href slot
			JSONArray fieldHrefSlots = new JSONArray();
			// 构造 columns
			JSONArray columns = new JSONArray();
			for (OnlCgreportItem item : list) {
				JSONObject column = new JSONObject(4);
				column.put("title", item.getFieldTxt());
				column.put("dataIndex", item.getFieldName());
				column.put("align", "center");
				column.put("sorter", "true");
				// update-begin--Author:sunjianlei  Date:20200103  for：判断有没有传href，如果传了就用slot
				if (StringUtils.isNotBlank(item.getFieldHref())) {
					String slotName = "fieldHref_" + item.getFieldName();
					JSONObject scopedSlots = new JSONObject();
					scopedSlots.put("customRender", slotName);
					column.put("scopedSlots", scopedSlots);

					JSONObject slot = new JSONObject();
					slot.put("slotName", slotName);
					slot.put("href", item.getFieldHref());
					fieldHrefSlots.add(slot);
				}
				// update-end--Author:sunjianlei  Date:20200103  for：判断有没有传href，如果传了就用slot
				columns.add(column);

				//字典项加载
				String dictCode = item.getDictCode();
                List<DictModel> ls = this.getDynDict(dictCode,records,item.getFieldName());
                if (ls != null) {
                    dictOptions.put(item.getFieldName(), ls);
                    column.put("customRender", item.getFieldName());
                }

			}

			Map<String, Object> resultMap = new HashMap<>(3);
			resultMap.put("data", result.getResult());
			resultMap.put("columns", columns);
			resultMap.put("dictOptions", dictOptions);//字典信息
			resultMap.put("fieldHrefSlots", fieldHrefSlots);//href信息
			resultMap.put("cgreportHeadName", head.getName());

			return Result.ok(resultMap);
		}
		return result;
	}


    /**
     * 根据字典code获取字典
     * @param dictCode
     * @return
     */
	private List<DictModel> getDynDict(String dictCode,JSONArray records,String fieldName){
        List<DictModel> ls = null;
        if (oConvertUtils.isNotEmpty(dictCode)) {
            // 判断字典是否是 select 开头的，如果是就认定为是一个 sql 语句，执行该语句获取数据并当做字典使用
            if (dictCode.trim().toLowerCase().indexOf("select ") == 0 && (fieldName==null || records.size() > 0) ) {
                // 此处原来的判断是dictCode.toLowerCase().indexOf("select ") == 0 && records.size() > 0
				//但是可以传null的records,

            	// 去除空格和最后一个分号
                dictCode = dictCode.trim();
                int lastIndex = dictCode.lastIndexOf(";");
                if (lastIndex == dictCode.length() - 1) {
                    dictCode = dictCode.substring(0, lastIndex);
                }
                // 拼接查询SQL
                String selectSql = "SELECT * FROM (" + dictCode + ") temp ";
                // 只取需要的值，防止字典SQL一下查询出所有的数据
                if(records!=null){
					List<String> inValues = new ArrayList<>();
					for (int i = 0; i < records.size(); i++) {
						JSONObject data = records.getJSONObject(i);
						String value = data.getString(fieldName);
						if (StringUtils.isNotBlank(value)) {
							inValues.add(value);
						}
					}
					String inValueStr = "'" + StringUtils.join(inValues, "','") + "'";
					selectSql += "WHERE temp.value IN (" + inValueStr + ")";
				}
                List<Map<?, ?>> queryDictList = onlCgreportHeadService.getBaseMapper().executeSelete(selectSql);
                if (queryDictList != null && queryDictList.size() != 0) {
                    String queryDictJson = JSON.toJSONString(queryDictList);
                    ls = JSON.parseArray(queryDictJson, DictModel.class);
                }
            } else {
                ls = sysBaseAPI.queryDictItemsByCode(dictCode);
            }
        }
        return ls;
    }

	/**
	 * 通过 head code 获取 所有的 item，并生成 columns 所需的 json
	 * 通过 head code 获取 sql语句，并执行该语句返回查询数据
	 *
	 * @param code
	 *            head code
	 * @return
	 * @throws Exception
	 */
	@Deprecated
	@GetMapping(value = "/getColumns/{code}")
	public Result<?> getColumns(@PathVariable("code") String code) {
		OnlCgreportHead head = onlCgreportHeadService.getById(code);
		if (head == null) {
			return Result.error("实体不存在");
		}
		QueryWrapper<OnlCgreportItem> queryWrapper = new QueryWrapper<OnlCgreportItem>();
		queryWrapper.eq("cgrhead_id", code);
		queryWrapper.eq("is_show", 1);
		queryWrapper.orderByAsc("order_num");

		List<OnlCgreportItem> list = onlCgreportItemService.list(queryWrapper);

		List<Map<String, String>> array = new ArrayList<Map<String, String>>();
		Map<String, Object> dictOptions = new HashMap<>();
		for (OnlCgreportItem item : list) {
			Map<String, String> column = new HashMap<String, String>(3);
			column.put("title", item.getFieldTxt());
			column.put("dataIndex", item.getFieldName());
			column.put("align", "center");
			column.put("sorter", "true");
			array.add(column);

			//字典项加载
			String dictCode = item.getDictCode();
			// update-begin--Author:sunjianlei Date:20190912 for：新增 sql 字典配置方式
            if (oConvertUtils.isNotEmpty(dictCode)) {
                List<DictModel> ls = null;
                // 判断字典是否是 select 开头的，如果是就认定为是一个 sql 语句，执行该语句获取数据并当做字典使用
                if (dictCode.toLowerCase().indexOf("select ") == 0) {
                    List<Map<?, ?>> queryDictList = onlCgreportHeadService.getBaseMapper().executeSelete(dictCode);
                    if (queryDictList != null && queryDictList.size() != 0) {
                        String queryDictJson = JSON.toJSONString(queryDictList);
                        ls = JSON.parseArray(queryDictJson, DictModel.class);
                    }
                } else {
                    ls = sysBaseAPI.queryDictItemsByCode(dictCode);
                }

                if (ls != null) {
                    dictOptions.put(item.getFieldName(), ls);
                    column.put("customRender", item.getFieldName());
                }
            }
			// update-end--Author:sunjianlei Date:20190912 for：新增 sql 字典配置方式
		}

		Map<String, Object> result = new HashMap<String, Object>(1);
		result.put("columns", array);
		result.put("dictOptions", dictOptions);//字典信息
		result.put("cgreportHeadName", head.getName());

		return Result.ok(result);
	}

	/**
	 * 通过 head code 获取 sql语句，并执行该语句返回查询数据
	 *
	 * @param code
	 * @return
	 */
	@GetMapping(value = "/getData/{code}")
	@PermissionData
	public Result<?> getData(@PathVariable("code") String code, HttpServletRequest request) {
		OnlCgreportHead head = onlCgreportHeadService.getById(code);
		if (head == null) {
			return Result.error("实体不存在");
		}
		String cgrSql = head.getCgrSql().trim();
		//加载数据权限配置 和 系统内置变量
		//String sql = SqlUtil.rewriteReportSql(cgrSql);
		String dbKey = head.getDbSource();
		try {
			Map<String, Object> params = SqlUtil.getParameterMap(request);
			Map<String, Object> reslutMap;
			if (StringUtils.isNotBlank(dbKey)) {
                log.info("Online报表: 走了多数据源逻辑");
				reslutMap = onlCgreportHeadService.executeSelectSqlDynamic(dbKey, cgrSql, params, head.getId());
			} else {
			    log.info("Online报表: 走了稳定逻辑");
                reslutMap = onlCgreportHeadService.executeSelectSql(cgrSql, head.getId(), params);
			}
			return Result.ok(reslutMap);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return Result.ok("报表加载成功！");
		}
	}


	/**
	 * 获取查询条件
	 */
	@GetMapping(value = "/getQueryInfo/{code}")
	public Result<?> getQueryInfo(@PathVariable("code") String cgrheadId) {
	    try {
	    	List<Map<String,String>> list = onlCgreportItemService.getAutoListQueryInfo(cgrheadId);
			return Result.ok(list);
		} catch (Exception e) {
			log.info(e.getMessage(),e);
			return Result.error("查询失败");
		}
	}

	/**
	 * 获取报表参数
	 */
	@GetMapping(value = "/getParamsInfo/{code}")
	public Result<?> getParamsInfo(@PathVariable("code") String cgrheadId) {
	    try {
	    	LambdaQueryWrapper<OnlCgreportParam> query = new LambdaQueryWrapper<OnlCgreportParam>();
	    	query.eq(OnlCgreportParam::getCgrheadId, cgrheadId);
	    	List<OnlCgreportParam> list = onlCgreportParamService.list(query);
			return Result.ok(list);
		} catch (Exception e) {
			log.info(e.getMessage(),e);
			return Result.error("查询失败");
		}
	}


	/**
	 * 将报表导出为excel
	 * @param request
	 * @param response
	 */
	@PermissionData
	@RequestMapping(value = "/exportXls/{reportId}")
	public void exportXls(@PathVariable("reportId") String reportId,HttpServletRequest request,HttpServletResponse response) {
		//step.1 设置，获取配置信息
		String codedFileName = "报表";
		String sheetName="导出信息";
		if (oConvertUtils.isNotEmpty(reportId)) {
			Map<String, Object> cgReportMap = null;
			try{
				cgReportMap = onlCgreportHeadService.queryCgReportConfig(reportId);
			}catch (Exception e) {
				throw new JeecgBootException("动态报表配置不存在!");
			}
			List<Map<String,Object>> fieldList = (List<Map<String, Object>>) cgReportMap.get(CgReportConstant.ITEMS);
			// 根据code获取所有数据
			Result res = this.getData(reportId, request);
            List<Map<String, Object>> records = null;
			if (res.getCode().equals(200)) {
				Map<String, Object> reslutMap = (Map<String, Object>) res.getResult();
                records = (List<Map<String, Object>>) reslutMap.get("records");
			}
//			Map<String, Object> configM = (Map<String, Object>) cgReportMap.get(CgReportConstant.MAIN);
//			codedFileName = configM.get("name")+codedFileName;
//			String querySql = (String) configM.get(CgReportConstant.CONFIG_SQL);
//			List<Map<String,Object>> items = (List<Map<String, Object>>) cgReportMap.get(CgReportConstant.ITEMS);
//			//页面查询条件
//			Map<String, Object> params = SqlUtil.getParameterMap(request);
//			List<OnlCgreportParam> paramList = (List<OnlCgreportParam>) cgReportMap.get(CgReportConstant.PARAMS);
//			//页面参数查询字段（占位符的条件语句）
//			Map<String, Object> pageSearchFields =  new LinkedHashMap<String,Object>();
//			//step.2 装载查询条件
//			Map<String,Object> paramData = new HashMap<String, Object>();
//			if(paramList!=null&&paramList.size()>0){
//                for (OnlCgreportParam paramConfig : paramList) {
//                    //前端传值为了避免 SQL自带的参数和查询条件参数弄混了 故而将SQL自带的参数名加前缀:self_
//                    Object valueObj = request.getParameter("self_" + paramConfig.getParamName());
//                    String value = "";
//                    if (valueObj != null) {
//                        value = valueObj.toString();
//                    } else if (oConvertUtils.isNotEmpty(paramConfig.getParamValue())) {
//                        value = paramConfig.getParamValue();
//                    }
//                    querySql = querySql.replace("${" + paramConfig.getParamName() + "}", value);
//                    //TODO 此处代码无用，可以删掉
//                    paramData.put(paramConfig.getParamName(), value);
//                }
//            }
//			for(Map<String,Object> item:items){
//				String isQuery = item.get(CgReportConstant.ITEM_ISQUERY).toString();
//				if(CgReportConstant.BOOL_TRUE.equalsIgnoreCase(isQuery)){
//					CgReportQueryParamUtil.loadQueryParams(request, item, pageSearchFields,paramData);
//				}
//			}
//			//step.3 进行查询返回结果
//            String dbKey=(String)configM.get("db_source");
//            List<Map<?, ?>> result=null;
//            if(oConvertUtils.isNotEmpty(dbKey)){
//            	if(paramData!=null&&paramData.size()>0){
//            		//TODO 动态数据源功能未实现
//            		//result= DynamicDBUtil.findListByHash(dbKey,SqlUtil.getFullSql(querySql,pageSearchFields),(HashMap<String, Object>)paramData);
//            	}else{
//            		//result= DynamicDBUtil.findList(dbKey, SqlUtil.getFullSql(querySql,pageSearchFields));
//            	}
//            }else{
//                result= onlCgreportHeadService.queryByCgReportSql(querySql, pageSearchFields,paramData, -1, -1);
//            }



			// Step.4 组装AutoPoi所需参数
			List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
			for (int i = 0; i < fieldList.size(); i++) {
				// 过滤不展示的字段
				if ("1".equals(oConvertUtils.getString(fieldList.get(i).get("is_show")))) {
					//update-begin-author:taoyan date:20190930 for:oracle数据库 online报表导出异常
					String exportFieldName = fieldList.get(i).get("field_name").toString();
					ExcelExportEntity expEntity = new ExcelExportEntity(fieldList.get(i).get("field_txt").toString(),exportFieldName , 15);
					//update-end-author:taoyan date:20190930 for:oracle数据库 online报表导出异常

					//1. 字典值设置
					Object dictCode = fieldList.get(i).get("dict_code");
                    JSONArray records_j = JSONObject.parseArray(JSONObject.toJSONString(records));
                    List<DictModel> ls = this.getDynDict(oConvertUtils.getString(dictCode),records_j,exportFieldName);
                    if(ls!=null && ls.size()>0){
                        List<String> dictReplaces = new ArrayList<String>();
						for (DictModel d : ls) {
							dictReplaces.add(d.getText()+ "_" + d.getValue());
						}
						//男_1, 女_2
						expEntity.setReplace(dictReplaces.toArray(new String[dictReplaces.size()]));
                    }
//					if (oConvertUtils.isNotEmpty(dictCode)) {
//						List<String> dictReplaces = new ArrayList<String>();
//						List<DictModel> dictList = sysBaseAPI.queryDictItemsByCode(dictCode.toString());
//						for (DictModel d : dictList) {
//							dictReplaces.add(d.getText()+ "_" + d.getValue());
//						}
//						//男_1, 女_2
//						expEntity.setReplace(dictReplaces.toArray(new String[dictReplaces.size()]));
//					}
					//2. 取值表达式(男_1, 女_2)
					//TODO oracle下大小写兼容问题
					Object replace_val = fieldList.get(i).get("replace_val");
					if(oConvertUtils.isNotEmpty(replace_val)) {
						expEntity.setReplace(replace_val.toString().split(","));
					}
					entityList.add(expEntity);
				}
			}

			//---------------------------------------------------------------------------------------------------------------------
			response.setContentType("application/vnd.ms-excel");
			OutputStream fOut = null;
			try {

				// step.4 根据浏览器进行转码，使其支持中文文件名
				String browse = BrowserUtils.checkBrowse(request);
				if ("MSIE".equalsIgnoreCase(browse.substring(0, 4))) {
					response.setHeader("content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(codedFileName, "UTF-8") + ".xls");
				} else {
					String newtitle = new String(codedFileName.getBytes("UTF-8"), "ISO8859-1");
					response.setHeader("content-disposition", "attachment;filename=" + newtitle + ".xls");
				}
				// step.5 产生工作簿对象
				//HSSFWorkbook workbook = cgReportExcelService.exportExcel(codedFileName, fieldList, result);
				Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(null,sheetName), entityList, records);
				fOut = response.getOutputStream();
				workbook.write(fOut);
			} catch (Exception e) {

			} finally {
				try {
					fOut.flush();
					fOut.close();
				} catch (Exception e) {

				}
			}
			//---------------------------------------------------------------------------------------------------------------------

		} else {
			throw new JeecgBootException("参数错误");
		}
	}



	/*   ------------------------------------------------------------POPUP------------------------------------------------------------   */
	@GetMapping(value = "/getRpColumns/{code}")
	public Result<?> getRpColumns(@PathVariable("code") String code) {
		LambdaQueryWrapper<OnlCgreportHead> query = new LambdaQueryWrapper<OnlCgreportHead>();
		query.eq(OnlCgreportHead::getCode, code);
		OnlCgreportHead head = onlCgreportHeadService.getOne(query);
		if (head == null) {
			return Result.error("实体不存在");
		}
		QueryWrapper<OnlCgreportItem> queryWrapper = new QueryWrapper<OnlCgreportItem>();
		queryWrapper.eq("cgrhead_id", head.getId());
		queryWrapper.eq("is_show", 1);
		queryWrapper.orderByAsc("order_num");

		List<OnlCgreportItem> list = onlCgreportItemService.list(queryWrapper);

		List<Map<String, String>> array = new ArrayList<Map<String, String>>();
		Map<String, Object> dictOptions = new HashMap<>();
		for (OnlCgreportItem item : list) {
			Map<String, String> column = new HashMap<String, String>(3);
			column.put("title", item.getFieldTxt());
			column.put("dataIndex", item.getFieldName());
			column.put("align", "center");

			String dbtype = item.getFieldType();
			if("Integer".equals(dbtype)||"Date".equals(dbtype)||"Long".equals(dbtype)) {
				column.put("sorter","true");
			}

			//字典项加载(也可以只把字典的信息存下来 返回到前端 在前端在做新的请求获取字典项)
			String dictCode = item.getDictCode();
			if(oConvertUtils.isNotEmpty(dictCode)) {
				List<DictModel> ls = this.getDynDict(item.getDictCode(), null, null);
				dictOptions.put(item.getFieldName(),ls);
				column.put("customRender", item.getFieldName());
			}
			array.add(column);
		}

		Map<String, Object> result = new HashMap<String, Object>(1);
		result.put("columns", array);
		result.put("dictOptions", dictOptions);
		result.put("cgRpConfigId", head.getId());
		result.put("cgRpConfigName", head.getName());
		return Result.ok(result);
	}

    /**
     * 测试数据源连接
     * @param dbSource
     * @return
     */
    @PostMapping("/testConnection")
    public Result testConnection(@RequestBody DynamicDataSourceModel dbSource) {
        Connection conn = null;
        try {
            Class.forName(dbSource.getDbDriver()); //加载及注册JDBC驱动程序
            //建立连接对象
            conn = DriverManager.getConnection(dbSource.getDbUrl(), dbSource.getDbUsername(), dbSource.getDbPassword());
            if (conn != null) {
                return Result.ok("数据库连接成功");
            }
            return Result.ok("数据库连接失败：错误未知");
        } catch (ClassNotFoundException e) {
            log.error(e.toString());
            return Result.error("数据库连接失败：驱动类不存在");
        } catch (Exception e) {
            log.error(e.toString());
            return Result.error("数据库连接失败：" + e.getMessage());
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.toString());
            }
        }
    }

}
